# !/usr/bin/env python
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name:     yaml_handler.py
   Description:   企业级 EXCEL 操作工具类
-------------------------------------------------
"""
import logging
from pathlib import Path
from typing import Any, Optional, List, Union

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment
from openpyxl.worksheet.worksheet import Worksheet

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(name)s - %(message)s",
)
logger = logging.getLogger("ExcelHandler")

# 模块公开接口
__all__ = ["ExcelHandler", "excel_handler"]


class ExcelHandler:
    """
    企业级 Excel 操作工具类
    提供 Excel 文件的创建、读取、写入、删除等功能。
    """

    @staticmethod
    def create_excel(filepath: Union[str, Path]) -> None:
        """
        创建一个空的 Excel 文件。

        :param filepath: Excel 文件路径
        """
        filepath = Path(filepath)
        logger.info(f"Creating Excel file: {filepath}")
        try:
            workbook = Workbook()
            workbook.save(filepath)
            logger.debug(f"Excel file created successfully: {filepath}")
        except Exception as e:
            logger.error(f"Failed to create Excel file: {filepath} | Error: {e}")
            raise

    @staticmethod
    def read_excel(filepath: Union[str, Path], sheet_name: Optional[str] = None) -> List[List[Any]]:
        """
        读取 Excel 文件内容。

        :param filepath: Excel 文件路径
        :param sheet_name: 工作表名称（默认读取第一个工作表）
        :return: 工作表内容，列表格式，每行是一个列表
        """
        filepath = Path(filepath)
        logger.info(f"Reading Excel file: {filepath}")
        if not filepath.exists():
            logger.error(f"File not found: {filepath}")
            raise FileNotFoundError(f"File not found: {filepath}")

        try:
            workbook = load_workbook(filepath, data_only=True)
            sheet = workbook[sheet_name] if sheet_name else workbook.active
            data = [[cell.value for cell in row] for row in sheet.iter_rows()]
            logger.debug(f"Excel file read successfully: {filepath}")
            return data
        except Exception as e:
            logger.error(f"Failed to read Excel file: {filepath} | Error: {e}")
            raise

    @staticmethod
    def write_to_excel(
            filepath: Union[str, Path],
            data: List[List[Any]],
            sheet_name: Optional[str] = None,
            start_row: int = 1,
            start_col: int = 1,
    ) -> None:
        """
        写入数据到 Excel 文件。

        :param filepath: Excel 文件路径
        :param data: 数据列表，二维数组形式
        :param sheet_name: 工作表名称（默认写入第一个工作表）
        :param start_row: 起始行
        :param start_col: 起始列
        """
        filepath = Path(filepath)
        logger.info(f"Writing to Excel file: {filepath}")
        if not filepath.exists():
            logger.warning(f"File not found, creating a new one: {filepath}")
            ExcelHandler.create_excel(filepath)

        try:
            workbook = load_workbook(filepath)
            sheet = workbook[sheet_name] if sheet_name else workbook.active

            for i, row in enumerate(data, start=start_row):
                for j, value in enumerate(row, start=start_col):
                    sheet.cell(row=i, column=j, value=value)

            workbook.save(filepath)
            logger.debug(f"Data written successfully to Excel file: {filepath}")
        except Exception as e:
            logger.error(f"Failed to write to Excel file: {filepath} | Error: {e}")
            raise

    @staticmethod
    def add_sheet(filepath: Union[str, Path], sheet_name: str) -> None:
        """
        添加一个新的工作表。

        :param filepath: Excel 文件路径
        :param sheet_name: 新工作表名称
        """
        filepath = Path(filepath)
        logger.info(f"Adding sheet '{sheet_name}' to Excel file: {filepath}")
        try:
            workbook = load_workbook(filepath)
            if sheet_name in workbook.sheetnames:
                logger.warning(f"Sheet '{sheet_name}' already exists in {filepath}")
                return
            workbook.create_sheet(sheet_name)
            workbook.save(filepath)
            logger.debug(f"Sheet '{sheet_name}' added successfully")
        except Exception as e:
            logger.error(f"Failed to add sheet '{sheet_name}' | Error: {e}")
            raise

    @staticmethod
    def delete_sheet(filepath: Union[str, Path], sheet_name: str) -> None:
        """
        删除指定工作表。

        :param filepath: Excel 文件路径
        :param sheet_name: 要删除的工作表名称
        """
        filepath = Path(filepath)
        logger.info(f"Deleting sheet '{sheet_name}' from Excel file: {filepath}")
        try:
            workbook = load_workbook(filepath)
            if sheet_name not in workbook.sheetnames:
                logger.warning(f"Sheet '{sheet_name}' does not exist in {filepath}")
                return
            del workbook[sheet_name]
            workbook.save(filepath)
            logger.debug(f"Sheet '{sheet_name}' deleted successfully")
        except Exception as e:
            logger.error(f"Failed to delete sheet '{sheet_name}' | Error: {e}")
            raise

    @staticmethod
    def format_cell(
            sheet: Worksheet,
            cell: str,
            font_name: str = "Arial",
            font_size: int = 12,
            bold: bool = False,
            align: str = "center",
    ) -> None:
        """
        格式化单元格样式。

        :param sheet: 工作表对象
        :param cell: 单元格位置（如 'A1'）
        :param font_name: 字体名称
        :param font_size: 字体大小
        :param bold: 是否加粗
        :param align: 对齐方式（'center', 'left', 'right'）
        """
        logger.info(f"Formatting cell '{cell}'")
        try:
            alignment = {"center": Alignment(horizontal="center"),
                         "left": Alignment(horizontal="left"),
                         "right": Alignment(horizontal="right")}.get(align, Alignment(horizontal="center"))
            sheet[cell].font = Font(name=font_name, size=font_size, bold=bold)
            sheet[cell].alignment = alignment
            logger.debug(f"Cell '{cell}' formatted successfully")
        except Exception as e:
            logger.error(f"Failed to format cell '{cell}' | Error: {e}")
            raise


# 全局实例化，便于外部调用
excel_handler = ExcelHandler()

# 示例用法
if __name__ == "__main__":
    # 创建 Excel 文件并写入数据
    try:
        excel_handler.create_excel("example.xlsx")
        excel_handler.write_to_excel("example.xlsx", [["Name", "Age"], ["Alice", 30], ["Bob", 25]])
        data = excel_handler.read_excel("example.xlsx")
        print(data)
    except Exception as e:
        logger.error(e)

    # 添加和删除工作表
    try:
        excel_handler.add_sheet("example.xlsx", "Sheet2")
        excel_handler.delete_sheet("example.xlsx", "Sheet2")
    except Exception as e:
        logger.error(e)
